<?php 
Class report_Model_DbQuery extends Zend_Db_Table_Abstract{
	
	public function getSalesItem($data){
		$db=$this->getAdapter();
		//$start_date = strtotime($start);
		//$end_date =strtotime($end)+86400;//bonus 24h/day
		
// 		echo"after ";
// 		echo $sec=strtotime($star);
// 		echo "time to date".date('Y/m/d H:i:s', $sec);exit();
		$start_date = trim($data["start_date"]);
		$end_date = trim($data["end_date"]);
		
		$sql = "SELECT p.item_name, p.p_code, SUM( si.qty_order ) AS qty
					FROM tb_product AS p, tb_sales_order_item AS si, tb_sales_order AS s
					WHERE p.pro_id = si.pro_id
					AND si.order_id = s.order_id AND s.status=4 AND s.date_order BETWEEN '$start_date' AND '$end_date'";
			if(($data["item"]!="" AND $data["item"]!=0 )){
				//$sql.=" AND "."(p.item_name LIKE '%".trim($data["item"])."%' OR p.p_code LIKE '%".trim($data["item"]."%')");
				$sql.=" AND p.pro_id = ".trim($data["item"]);
			}
			if($data["LocationId"]!="" AND $data["LocationId"]!=0){
				$sql.=" AND s.LocationId = ".trim($data["LocationId"]);
			}
		$sql.=" GROUP BY si.pro_id ";
		return $db->fetchAll($sql);
	}
	//get location name for report at other location
	public function getLocationName($location_id){
		$db=$this->getAdapter();
		$sql="SELECT Name FROM tb_sublocation WHERE LocationId = ".$location_id;
		$row=$db->fetchRow($sql);
		return $row;
	}
	public function getPurchaseItem($data){
		$db=$this->getAdapter();
		$start_date = trim($data["start_date"]);
		$end_date = trim($data["end_date"]);
		
		$sql = "SELECT p.item_name, p.p_code, SUM( pi.qty_order ) AS qty
				FROM tb_product AS p, tb_purchase_order_item AS pi, tb_purchase_order AS pur
				WHERE p.pro_id = pi.pro_id
				AND pi.order_id = pur.order_id
				AND pur.status = 4
				AND pur.date_order BETWEEN '$start_date' AND '$end_date'";
		
			if(($data["item"]!="" AND $data["item"]!=0 )){
				$sql.=" AND p.pro_id = ".trim($data["item"]);
			}
			if($data["LocationId"]!="" AND $data["LocationId"]!=0){
				$sql.=" AND s.LocationId = ".trim($data["LocationId"]);
			}
		
				$sql.=" GROUP BY pi.pro_id ";
		return $db->fetchAll($sql);
	}
	
}

?>